﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Script.Serialization;
using Microsoft.ApplicationBlocks.Data;
using EnglishRemix.Services.Model;
using EnglishRemix.Services.Utility;
using Newtonsoft.Json;
using System.Text.RegularExpressions;

using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;
using System.Security.Permissions;
using System.Threading.Tasks;
using AlpineSoft;
using System.Diagnostics;
using EnglishRemix.Services.Model.Requests;
using System.Data.SqlClient;
using EnglishRemix.Services.Model.Exceptions;
using EnglishRemix.Services.Constants;
using EnglishRemix.Services.Model.Responses;
using System.IO;

namespace EnglishRemix.Services.Facade
{
    public class UserFacade
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        string sqlStr;

        public UserFacade()
        {
            log.Debug("UserFacade initialized");
        }

        public RegistrationResponse RegisterUser(RegistrationRequest request)
        {
            RegistrationResponse resp = new RegistrationResponse();

            // Check repeated users
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName);

            DataSet ds = DBUtility.ExecuteDataset("Select * from USERINFO where Name = @Name", sqlParam);
            if (DBUtility.hasResult(ds))
            {
                // If a user with the same name provided in the request already exists.
                throw new ServiceException(ResponseMessages.RES_REG_FAIL_USER_EXISTS);
            }
            log.Debug("Creating new user now...");

            sqlParam.Clear();
            DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName);
            DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, request.password);
            DBUtility.AddSqlParam(sqlParam, "@Email", SqlDbType.NVarChar, "");
            DBUtility.AddSqlParam(sqlParam, "@ActivationStatus", SqlDbType.SmallInt, 1);
            DBUtility.AddSqlParam(sqlParam, "@Level", SqlDbType.SmallInt, 1);
            DBUtility.AddSqlParam(sqlParam, "@Coin", SqlDbType.BigInt, 0);
            DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
            DBUtility.AddSqlParam(sqlParam, "@LastLoginDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
            DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");

            sqlStr = "INSERT INTO USERINFO (Name, Password, Email, ActivationStatus, Level, Coin, CreatedOn, LastLoginDate, LastModifiedBy) VALUES (" +
                "@Name, @Password, @Email, @ActivationStatus, @Level, @Coin, @CreatedOn, @LastLoginDate, @LastModifiedBy);";

            try
            {
                // Send a command to create a user.
                // If this step is done, a user will be created in the database regardless of method the user chose for the registration process.
                log.Debug("Sending command to create user. SQL:" + sqlStr);
                if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                {
                    throw new ServiceException("Registration failed. Reason: User creation failed.");
                }

                // If registration is successful, get the newest UserInfo.
                UserInfo userInfo = new UserInfo();
                LoginRequest userInfoReq = new LoginRequest();
                userInfoReq.UserName = request.UserName;
                userInfoReq.Password = request.password;
                userInfoReq.Route = request.Route;
                LoginResponse userInfoRes = new LoginResponse();
                userInfoRes = LoginUser(userInfoReq);

                // Takes care of the credentials
                switch (request.Route)
                {
                    case "0":
                        {   // If user is created with a native credential, user registration is done. Return the result and an UserInfo object.
                            // Create a UserInfo Object
                            resp.Result = true;
                            resp.Message = "User successfully created with a native account.";
                            resp.User = userInfoRes.user;
                            return resp;
                        }
                    case "1":   // If user is created with a third party credential, contiune to update the credential in the table CREDENTIALS.
                    case "2":
                        break;
                    default:
                        {    // If the route is unrecognized
                            throw new ServiceException("Registration failed. Reason: Unrecognized route. (b)");
                        }
                }

                sqlParam.Clear();

                // If user is created with a third party credential, update the credential in the table CREDENTIALS.
                string lastest_id = DBUtility.ExecuteDataset("SELECT TOP 1 Id FROM USERINFO ORDER BY Id DESC", new List<SqlParameter>()).Tables[0].Rows[0]["Id"].ToString();
                DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.NVarChar, lastest_id);
                DBUtility.AddSqlParam(sqlParam, "@SocialNetwork", SqlDbType.NVarChar, request.Route);
                DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName);
                string updateCredStr = "INSERT INTO CREDENTIALS (UserInfo, SocialNetwork, Name) VALUES (@UserInfo, @SocialNetwork, @Name);";

                // Send a command to update user's third party credential.
                if (DBUtility.ExecuteNonQuery(updateCredStr, sqlParam) != 1)
                {
                    throw new ServiceException(ResponseMessages.RES_LOGIN_FAIL_TO_UPDATE);
                }

                resp.Result = true;
                resp.Message = ResponseMessages.RES_REG_SUCC_THIRD_PARTY;
                resp.User = userInfoRes.user;
                return resp;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public LoginResponse LoginUser(LoginRequest request)
        {
            string sqlStr = "";
            LoginResponse userResponse = new LoginResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName);
            DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, request.Password);
            DBUtility.AddSqlParam(sqlParam, "@Route", SqlDbType.NVarChar, request.Route);

            // Identify the route and corresponding credential. 
            // If there is a match, then the user is authenticated, return user info.
            switch (request.Route)
            {
                case "0":
                    {
                        sqlStr = "SELECT * FROM USERINFO where Name = @Name and Password = @Password;";
                        break;
                    }
                case "1":
                case "2":
                    {
                        sqlStr = "SELECT USERINFO.* FROM USERINFO, CREDENTIALS " +
                                    "where USERINFO.usr_id = CREDENTIALS.usr_id " +
                                    "and CREDENTIALS.credential_type = @Route and CREDENTIALS.credential_id = @Route;";
                        break;
                    }
                default:
                    {    // Route error
                        throw new ServiceException(ResponseMessages.RES_REG_FAIL_WRONG_ROUTE);
                    }
            }

            try
            {
                DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                if (DBUtility.hasResult(ds))
                {
                    // Serialize the first user returned([0]) from database and send it back.
                    UserInfo user = new UserInfo();

                    // Get user's favorite List
                    List<FavoriteInfo> favList = new List<FavoriteInfo>();                    
                    user.FavoriteList = favList;

                    DataRow dataRow = ds.Tables[0].Rows[0];
                    user.UserId = dataRow["Id"].ToString();
                    user.UserName = dataRow["Name"].ToString();
                    user.Password = dataRow["Password"].ToString();
                    user.Email = dataRow["Email"].ToString();
                    user.ActivationStatus = dataRow["ActivationStatus"].ToString();
                    user.Level = dataRow["Level"].ToString();
                    user.Coin = dataRow["Coin"].ToString();
                    user.CreatedOn = ((DateTime)dataRow["LastLoginDate"]).ToString(ConfigurationManager.AppSettings["DateFormat"]);
                    user.LastLoginDate = ((DateTime)dataRow["LastLoginDate"]).ToString(ConfigurationManager.AppSettings["DateFormat"]);
                    userResponse.user = user;
                }
                else
                {
                    throw new ServiceException(ResponseMessages.RES_LOGIN_FAIL_NO_USER);
                }
                return userResponse;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
               
        public AchievementResponse GetUserAchievement(AchievementRequest achievementRequest)
        {
            AchievementResponse achievementResponse = new AchievementResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlString = @"Select 
                        ui.Id as UserId,    
                        ui.Name as UserName, 
                        ach.Name as AchievementName,
                        ach.Description as Description,
                        ach.ActionType as ActionType,
                        ach.Criteria as  Criteria,
                        ach.ResourceUrl as ResourceUrl
                        from UserAchievement  ua  
                        join Achievement ach on ua.Achievement = ach.Id
                        join UserInfo ui on ua.UserId = ui.Id
                        where ua.UserId =@userId";

            log.Debug("Getting UserAchievement now...");
            DBUtility.AddSqlParam(sqlParam, "@userId", System.Data.SqlDbType.BigInt, achievementRequest.UserId);
            DataSet ds = DBUtility.ExecuteDataset(sqlString, sqlParam);
            List<AchievementInfo> achievementInfos = new List<AchievementInfo>();
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    AchievementInfo achievementInfo = new AchievementInfo();
                    achievementInfo.UserId = Convert.ToInt32(dr["UserId"]);
                    achievementInfo.UserName = dr["UserName"].ToString();
                    achievementInfo.AchievementName = dr["AchievementName"].ToString();
                    achievementInfo.Description = dr["Description"].ToString();
                    achievementInfo.ActionType = Convert.ToInt32(dr["ActionType"]);
                    achievementInfo.Criteria = dr["Criteria"].ToString();
                    achievementInfo.ResourceUrl = dr["ResourceUrl"].ToString();                  
                    achievementInfos.Add(achievementInfo);
                }
                achievementResponse.AchievementList = achievementInfos;

            }
            return achievementResponse;
        }
        //Return all user achievements
        public AchievementResponse GetAllAchievements()
        {
            AchievementResponse achievementResponse = new AchievementResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();            
            string sqlStr = @"Select 
                                ui.Id as UserId,    
                                ui.Name as UserName, 
                                ach.Name as AchievementName,
                                ach.Description as Description,
                                ach.ActionType as ActionType,
                                ach.Criteria as  Criteria,
                                ach.ResourceUrl as ResourceUrl
                                from UserAchievement  ua  
                                join Achievement ach on ua.Achievement = ach.Id
                                join UserInfo ui on ua.UserId = ui.Id
                                order by UserId";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            List<AchievementInfo> achievementInfos = new List<AchievementInfo>();
            log.Debug("Getting all Achievements now...");
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    AchievementInfo achievementInfo = new AchievementInfo();
                    achievementInfo.UserId = Convert.ToInt32(dr["UserId"]);
                    achievementInfo.UserName = dr["UserName"].ToString();
                    achievementInfo.AchievementName = dr["AchievementName"].ToString();
                    achievementInfo.Description = dr["Description"].ToString();
                    achievementInfo.ActionType = Convert.ToInt32(dr["ActionType"]);
                    achievementInfo.Criteria = dr["Criteria"].ToString();
                    achievementInfo.ResourceUrl = dr["ResourceUrl"].ToString();                   
                    achievementInfos.Add(achievementInfo);
                }
                achievementResponse.AchievementList = achievementInfos;

            }
            return achievementResponse;
        }

        public UpdateActivityStatusResponse UpdateActivityStatusByUser(UpdateActivityStatusRequest updateActivityStatusRequest)
        {
            UpdateActivityStatusResponse resp = new UpdateActivityStatusResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();

            DBUtility.AddSqlParam(sqlParam, "@Status", SqlDbType.NVarChar, updateActivityStatusRequest.ActivityStatusName);
            string sqlStr1 = @"select Id from UserActivityStatus  where Status =@Status";
            int activityStatusId = 0;

            DataSet ds = DBUtility.ExecuteDataset(sqlStr1, sqlParam);
            if (DBUtility.hasResult(ds))
            {
                activityStatusId = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
            }
            sqlParam.Clear();
            DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, updateActivityStatusRequest.UserId);
            DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, updateActivityStatusRequest.ActivityId);

            string sqlStr2 = @"select * from UserActivity where
                            UserInfo =@UserInfo and Activity =@Activity ";

            try
            {
                DataSet ds2 = DBUtility.ExecuteDataset(sqlStr2, sqlParam);
                if (DBUtility.hasResult(ds2))
                {
                    sqlParam.Clear();
                    DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, updateActivityStatusRequest.UserId);
                    DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, updateActivityStatusRequest.ActivityId);
                    DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
                    DBUtility.AddSqlParam(sqlParam, "@Status", SqlDbType.BigInt, activityStatusId);
                    string sqlStr3 = @"update UserActivity set Status = @Status, LastModifiedDate = @LastModifiedDate where UserInfo=@UserInfo and Activity=@Activity ";
                    //if record exist, update it
                    if (DBUtility.ExecuteNonQuery(sqlStr3, sqlParam) == 1)
                    {
                        resp.Result = true;
                        resp.Message = "ActivityStatus successfully updated ";
                        return resp;
                    }
                    else
                    {
                        // return "Database update failed.";

                        throw new ServiceException("update database failed.");
                    }
                }
                else
                {
                    //record does not exist, insert a new one 
                    sqlParam.Clear();
                    DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, updateActivityStatusRequest.UserId);
                    DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, updateActivityStatusRequest.ActivityId);
                    DBUtility.AddSqlParam(sqlParam, "@Status", SqlDbType.BigInt, activityStatusId);
                    DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                    DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
                    DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");

                    string sqlStr4 = @"insert into UserActivity(UserInfo,Activity,Status,CreatedOn,LastModifiedDate,LastModifiedBy) 
                               values(@UserInfo,@Activity,@Status,@CreatedOn,@LastModifiedDate,@LastModifiedBy)";

                    if (DBUtility.ExecuteNonQuery(sqlStr4, sqlParam) == 1)
                    {
                        resp.Result = true;
                        resp.Message = "ActivityStatus  inserted";
                        return resp;
                    }
                    else
                    {
                        throw new ServiceException("insert into database failed.");
                    }
                }
            }

            catch (ServiceException e)
            {
                throw e;
            }
            catch (Exception e)
            {
                throw e;
            }

        }      

        /// <summary>
        /// Get All rows from UserInfo table
        /// </summary>
        /// <returns> List<UserInfo> </UserResponse></returns>
        public List<UserInfo> GetAllUserInfo()
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            List<UserInfo> userInfo = new List<UserInfo>(); ;
            var ds = DBUtility.ExecuteDataset("Select * from UserInfo", sqlParam);           
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    UserInfo user = new UserInfo();
                    user.UserId = dr["Id"].ToString();
                    user.UserName = dr["Name"].ToString();
                    user.Password = dr["Password"].ToString();
                    user.Email = dr["Email"].ToString();
                    user.ActivationStatus = dr["ActivationStatus"].ToString();
                    user.Level = dr["Level"].ToString();
                    user.Coin = dr["Coin"].ToString();
                    user.CreatedOn = ((DateTime)dr["CreatedOn"]).ToString("yyyy-MM-dd HH:mm:ss");
                    user.LastLoginDate = ((DateTime)dr["LastLoginDate"]).ToString("yyyy-MM-dd HH:mm:ss");

                    int userId = Convert.ToInt32(dr["Id"]);
                    user.FavoriteList = GetUserFavoriteListById(userId);                    
                    userInfo.Add(user);
                }
                //return userInfo;
            }           
            return userInfo;
        }

        private List<FavoriteInfo> GetUserFavoriteListById(int userId)
        {           
            List<FavoriteInfo> favoriteList = new List<FavoriteInfo>();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, userId);
            sqlStr = @"select 
                           uf.Id as Id,
                           uf.FavoriteName as FavoriteName,					 
                           pi.Number as PageNumber,                    
                           c.Number as ChapterNumber                           
					  from UserFavorites uf                                    				     					       
                           join PageInfo pi on pi.Id = uf.PageInfo                          
					       join Chapter c on c.Id = pi.Chapter					       
                           where uf.UserId = @UserId";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    FavoriteInfo info = new FavoriteInfo();
                    info.UserFavoriteId = Convert.ToInt32(dr["Id"]);
                    info.FavoriteName = dr["FavoriteName"].ToString();
                    info.PageNumber = Convert.ToInt32(dr["PageNumber"]);
                    info.ChapterNumber = Convert.ToInt32(dr["ChapterNumber"]);
                    favoriteList.Add(info);
                }
            }
            return favoriteList;            
        }

        /// <summary>
        /// Get user information 
        /// </summary>
        /// <param name="Id"> user ID</param>
        /// <returns> user information </returns>
        public UserResponse GetUserInfo(UserRequest request)
        {
            UserResponse userResp = new UserResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            UserInfo user = new UserInfo();
            if(request.UserId >0 || request.UserId == null)
            {
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, request.UserId);
                var ds = DBUtility.ExecuteDataset("Select * from UserInfo where id = @UserId", sqlParam);

                if (DBUtility.hasResult(ds))
                {
                    DataRow dr = ds.Tables[0].Rows[0];
                    user.UserId = dr["Id"].ToString();
                    user.UserName = dr["Name"].ToString();
                    user.Password = dr["Password"].ToString();
                    user.Email = dr["Email"].ToString();
                    user.ActivationStatus = dr["ActivationStatus"].ToString();
                    user.Level = dr["Level"].ToString();
                    user.Coin = dr["Coin"].ToString();
                    user.CreatedOn = ((DateTime)dr["CreatedOn"]).ToString("yyyy-MM-dd HH:mm:ss");
                    user.LastLoginDate = ((DateTime)dr["LastLoginDate"]).ToString("yyyy-MM-dd HH:mm:ss");
                    user.FavoriteList = GetUserFavoriteListById(request.UserId);

                    userResp.User = user;                    
                }
                return userResp;
            }
            else
            {
                throw new ServiceException("Invalid UserId: " + request.UserId);
            }
        }
        private void ValidateUserId(string Id)
        {
            try
            {
                Int64 number = Int64.Parse(Id);
                if (number < 1)
                    throw new ArgumentNullException("User ID must be greater than 0");
            }
            catch (FormatException)
            {
                throw new FormatException("User ID is not number. Unable to convert " + Id);
            }
            catch (OverflowException)
            {
                throw new OverflowException("Input sting is out of range: " + Id + ".");
            }
        }

        public UserActivityStatusResponse GetUserActivityStatus(UserActivityStatusRequest request)
        {
            UserActivityStatusResponse userActivityStatusResp = new UserActivityStatusResponse();
            List<ActivityInfo> userActivityInfoList = new List<ActivityInfo>();
            string sqlstr = null;
            List<SqlParameter> param = new List<SqlParameter>();
            if (request.ChapterNumber > 0)
            {
                sqlstr = @"Select
					         ua.Activity as ActivityId,
                             a.Number as ActivityNumber,
                             a.Price as Price,
                             a.Description as ActivityDescription, 
                             a.Type as ActivityType,
                             uas.Status as ActivityStatus,
                             a.Position as Position,
                             ar.Description as ResourceDescription,
                             ar.Name as ResourceName,
                             ar.Type as ResourceType,
                             ar.RootPath as ActivityRootPath,
                             ar.SubPath as ActivitySubPath
					         from UserActivity  ua
					         join UserInfo ui on ui.Id = ua.UserInfo
					         join UserActivityStatus uas on uas.Id = ua.Status 
					         join Activity a on a.Id = ua.Activity
					         join PageInfo pi on pi.Id = a.PageInfo
                             join Chapter c on c.Id = pi.Chapter
                             join ActivityResource ar on ar.Id = a.Resource
                             where ua.UserInfo = @userId AND c.Number = @ChapterNumber";
                DBUtility.AddSqlParam(param, "@ChapterNumber", System.Data.SqlDbType.BigInt, request.ChapterNumber);
                DBUtility.AddSqlParam(param, "@userId", System.Data.SqlDbType.BigInt, request.UserId);
            }
            else
            {
                sqlstr = @"Select
					         ua.Activity as ActivityId,
                             a.Number as ActivityNumber,
                             a.Price as Price,
                             a.Description as ActivityDescription, 
                             a.Type as ActivityType,
                             uas.Status as ActivityStatus,
                             a.Position as Position,
                             ar.Description as ResourceDescription,
                             ar.Name as ResourceName,
                             ar.Type as ResourceType,
                             ar.RootPath as ActivityRootPath,
                             ar.SubPath as ActivitySubPath
					         from UserActivity  ua
					         join UserInfo ui on ui.Id = ua.UserInfo
					         join UserActivityStatus uas on uas.Id = ua.Status 
					         join Activity a on a.Id = ua.Activity
					         join PageInfo pi on pi.Id = a.PageInfo
                             join Chapter c on c.Id = pi.Chapter
                             join ActivityResource ar on ar.Id = a.Resource
                             where ua.UserInfo = @userId";
                DBUtility.AddSqlParam(param, "@userId", System.Data.SqlDbType.BigInt, request.UserId);
            }
            DataSet ds = DBUtility.ExecuteDataset(sqlstr, param);
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    ActivityInfo userActivitiyInfo = new ActivityInfo();
                    userActivitiyInfo.ActivityId = Convert.ToInt32(dr["ActivityId"]);
                    userActivitiyInfo.ActivityNumber = Convert.ToInt32(dr["ActivityNumber"]);
                    userActivitiyInfo.Price = Convert.ToDecimal(dr["Price"]);
                    userActivitiyInfo.ActivityDescription = dr["ActivityDescription"].ToString();
                    userActivitiyInfo.ActivityType = dr["ActivityType"].ToString();
                    userActivitiyInfo.ActivityStatus = dr["ActivityStatus"].ToString();
                    userActivitiyInfo.Position = dr["Position"].ToString();
                    userActivitiyInfo.ResourceDescription = dr["ResourceDescription"].ToString();
                    userActivitiyInfo.ResourceName = dr["ResourceName"].ToString();
                    userActivitiyInfo.ResourceType = dr["ResourceType"].ToString();
                    userActivitiyInfo.ActivityRootPath = dr["ActivityRootPath"].ToString();
                    userActivitiyInfo.ActivitySubPath = dr["ActivitySubPath"].ToString();                    
                    userActivityInfoList.Add(userActivitiyInfo);
                }
                userActivityStatusResp.UserActivityStatusList = userActivityInfoList;
                return userActivityStatusResp;
            }
            return null;
        }
        /// <summary>
        /// Update User information by Id and return updated user information to response
        /// </summary>
        /// <param name="request"></param>
        /// <returns>UpdateUserResponse</returns>
        public UpdateUserResponse UpdateUser(UpdateUserRequest request)
        {
            UpdateUserResponse resp = new UpdateUserResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            resp.Message = null;
            resp.Result = false;

            if (request.UserId > 0)
            {
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, request.UserId);
                List<string> setString = new List<string>();

                if (request.UserName != null)
                {
                    DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, request.UserName);
                    setString.Add("Name = @Name");
                }
                if (request.Email != null)
                {
                    DBUtility.AddSqlParam(sqlParam, "@Email", SqlDbType.NVarChar, request.Email);
                    setString.Add("Email = @Email");
                }
                if (request.ActivationStatus > 0)
                {
                    DBUtility.AddSqlParam(sqlParam, "@ActivationStatus", SqlDbType.SmallInt, request.ActivationStatus);
                    setString.Add("ActivationStatus = @ActivationStatus");
                }
                if (request.Level > 0)
                {
                    DBUtility.AddSqlParam(sqlParam, "@Level", SqlDbType.SmallInt, request.Level);
                    setString.Add("Level = @Level");
                }
                if (request.Coin > 0)
                {
                    DBUtility.AddSqlParam(sqlParam, "@Coin", SqlDbType.BigInt, request.Coin);
                    setString.Add("Coin = @Coin");
                }

                if (setString.Count > 0)
                {
                    sqlStr = "Update UserInfo set" + " ";
                    foreach (string str in setString)
                    {
                        sqlStr += str + ",";
                    }
                    sqlStr = sqlStr.Remove(sqlStr.Length - 1);
                    sqlStr += " " + "Where Id = @UserId";
                    try
                    {
                        log.Debug("Update user information. SQL: " + sqlStr);
                        if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                        {
                            throw new ServiceException("Update User information failed.");
                        }
                        resp.Result = true;
                        resp.Message = "Update User Information successful!";
                        return resp;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
                else
                {
                    return resp; ;
                }
            }
            else
            {
                return resp; ;
            }
        }

        /// <summary>
        /// Update User password
        /// </summary>
        /// <param name="changePwdRequest"></param>
        /// <returns>UpdateUserResponse</returns>       
        public UpdatePasswordResponse UpdatePassword(UpdatePasswordRequest request)
        {
            UpdatePasswordResponse resp = new UpdatePasswordResponse();
            resp.Message = null;
            resp.Result = false;
            //Replace ' to " in password string
            if (request.UserId > 0 && request.Password != null)
            {
                List<SqlParameter> sqlParam = new List<SqlParameter>();
                string newPwd = request.Password.Replace("'", "\"");
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, request.UserId);
                DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, newPwd);
                sqlStr = "Update UserInfo set Password = @Password where Id = @UserId";
                try
                {
                    log.Debug("Update user password. SQL: " + sqlStr);
                    if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                    {
                        throw new ServiceException("Update user password failed. UserID: " + request.UserId);
                    }
                    resp.Result = true;
                    resp.Message = "Update user password successful!";
                    return resp;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            else
            {
                return resp; ;
            }
        }

        public UserActivityResponse PurchaseActivity(UserActivityRequest purchaseRequest)
        {
            UserActivityResponse resp = new UserActivityResponse();
            resp.Message = null;
            resp.result = false;
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            List<string> sqlComm = new List<string>();
            string sqlStr = null;
            DataSet ds = null;
            int coin = 0;
            int price = 0;
            //Check UserId and ActivityId valid input
            if (purchaseRequest.UserId > 0 || purchaseRequest.ActivityId > 0)
            {
                //Check userID
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, purchaseRequest.UserId);
                sqlStr = @"select * from UserInfo where Id = @UserId";
                DataSet userSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                //Check ActivityID
                sqlParam.Clear();
                DBUtility.AddSqlParam(sqlParam, "@ActivityId", SqlDbType.BigInt, purchaseRequest.ActivityId);
                sqlStr = @"select Enabled from Activity where Id = @ActivityId";
                DataSet activitySet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                if (DBUtility.hasResult(userSet))
                {
                    if (DBUtility.hasResult(activitySet) && (Convert.ToInt32(activitySet.Tables[0].Rows[0]["Enabled"]) > 0))
                    {
                        //Activity is available and User exist 
                        sqlParam.Clear();
                        DBUtility.AddSqlParam(sqlParam, "@UserInfo", SqlDbType.BigInt, purchaseRequest.UserId);
                        DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, purchaseRequest.ActivityId);
                        sqlStr = @"Select
                                     uas.Id as StatusId,					 
                                     uas.Status as StatusName,                    
                                     a.Price as ActivityPrice,
                                     ui.Coin as UserCoin
					                 from UserActivity ua            
					                     join UserInfo ui on ui.Id = ua.UserInfo
					                     join UserActivityStatus uas on uas.Id = ua.Status 
					                     join Activity a on a.Id = ua.Activity					     
                                     where ua.UserInfo = @UserInfo AND ua.Activity = @Activity";
                        ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                        if (DBUtility.hasResult(ds))
                        {
                            //If UserActivity table has this record, but not purchase yet, in the shopping cart. 
                            DataRow dr = ds.Tables[0].Rows[0];
                            //Hold user Coin value.
                            coin = Convert.ToInt32(dr["UserCoin"]);
                            if (Convert.ToInt32(dr["StatusId"]) == 1)
                            {
                                //if ActivityStatus =1: Not purchase; then user can purchase this activity
                                sqlParam.Clear();
                                //Check Activity price again: Get latest price again.
                                DBUtility.AddSqlParam(sqlParam, "@Id", SqlDbType.BigInt, purchaseRequest.ActivityId);
                                sqlStr = @"select price from Activity where Id = @Id";
                                DataSet priceSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);

                                if (DBUtility.hasResult(priceSet))
                                {   //re-Check the activity price again in DB
                                    price = Convert.ToInt32(priceSet.Tables[0].Rows[0]["price"]);
                                }
                                //Purchase activity if coin is enough
                                if (coin >= price)
                                {   //User coin is enough to purchase.
                                    //Purchase activity; set status = 2 in UserActivity table and update coin in UserInfo
                                    coin = coin - price;
                                    sqlParam.Clear();
                                    DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, purchaseRequest.UserId);
                                    DBUtility.AddSqlParam(sqlParam, "@ActivityId", SqlDbType.BigInt, purchaseRequest.ActivityId);
                                    DBUtility.AddSqlParam(sqlParam, "@Status", SqlDbType.BigInt, 2);
                                    DBUtility.AddSqlParam(sqlParam, "@Coin", SqlDbType.BigInt, coin);

                                    sqlComm.Add("Update UserActivity set Status=@Status where UserInfo = @UserId and Activity = @ActivityId");
                                    sqlComm.Add("Update UserInfo set Coin=@Coin where Id = @UserId");
                                    if (DBUtility.ExecuteBatchNonQuery(sqlComm, sqlParam) != 1)
                                    {
                                        throw new ServiceException("Update purchase transaction failed. UserID: " + purchaseRequest.UserId);
                                    }
                                    resp.result = true;
                                    resp.Message = "Activity ID: " + purchaseRequest.ActivityId + " " + "is purchased.";
                                }
                                else
                                {   //User coin is not enough; return false and not update Coin in UserInfo;
                                    //Set status: Not purchased;
                                    resp.result = false;
                                    resp.Message = "Activity ID: " + purchaseRequest.ActivityId + " " + "Not purchased; Not enough Coin";
                                }
                            }
                            else
                            {
                                //ActivityStatus = 2, 3 or 4; User not need to purchase again; User already unlock this activity;
                                resp.result = true;
                                resp.Message = "Activity ID: " + purchaseRequest.ActivityId + " " + "is already unlocked.";
                            }
                        }
                        else
                        {
                            //Not record in dataSet. Insert new record to UserActivity Table. 
                            sqlParam.Clear();
                            //Check Coin and price for insert in UserActivity Table
                            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, purchaseRequest.UserId);
                            sqlStr = @"Select Coin from UserInfo where Id = @UserId";
                            DataSet coinSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                            if (DBUtility.hasResult(coinSet))
                            {
                                coin = Convert.ToInt32(coinSet.Tables[0].Rows[0]["Coin"]);
                            }

                            sqlParam.Clear();
                            DBUtility.AddSqlParam(sqlParam, "@ActivityId", SqlDbType.BigInt, purchaseRequest.ActivityId);
                            sqlStr = @"Select Price from Activity where Id = @ActivityId";
                            DataSet priceSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                            if (DBUtility.hasResult(priceSet))
                            {
                                price = Convert.ToInt32(priceSet.Tables[0].Rows[0]["Price"]);
                            }

                            if (coin >= price)
                            {
                                // Enough coin to purchase
                                coin = coin - price;
                                sqlParam.Clear();
                                //Inser new record into UserActivity table and update Coin value in UserInfo table 
                                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, purchaseRequest.UserId);
                                DBUtility.AddSqlParam(sqlParam, "@ActivityId", SqlDbType.BigInt, purchaseRequest.ActivityId);
                                DBUtility.AddSqlParam(sqlParam, "@Status", SqlDbType.BigInt, 2);
                                DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now);
                                DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
                                DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");
                                DBUtility.AddSqlParam(sqlParam, "@Coin", SqlDbType.BigInt, coin);
                                sqlStr = @" Insert into UserActivity (UserInfo, Activity, Status, CreatedOn, LastModifiedDate, LastModifiedBy) 
                                                values (@UserId, @ActivityId, @Status, @CreatedOn, @LastModifiedDate, @LastModifiedBy);";
                                sqlComm.Add(sqlStr);
                                sqlComm.Add("Update UserInfo set Coin=@Coin where Id = @UserId");
                                if (DBUtility.ExecuteBatchNonQuery(sqlComm, sqlParam) != 1)
                                {
                                    throw new ServiceException("Insert UserActivity and purchase transaction failed. UserID: " + purchaseRequest.UserId);
                                }
                                resp.result = true;
                                resp.Message = "Activity ID: " + purchaseRequest.ActivityId + " " + "is purchased.";
                            }
                            else
                            {   //User coin is not enough; return false and not update Coin in UserInfo;
                                //Set status: Not purchased;
                                resp.result = false;
                                resp.Message = "Activity ID: " + purchaseRequest.ActivityId + " " + "Not purchased; Not enough Coin";
                            }
                        }
                    }
                    else
                    {
                        resp.result = false;
                        resp.Message = "Activity ID: " + purchaseRequest.ActivityId + " " + " is not available.";
                    }
                }
                else
                {
                    // Activity is not available
                    resp.result = false;
                    resp.Message = "User ID: " + purchaseRequest.UserId + " " + " is not Exist.";
                }
            }
            return resp;
        }
        //TODO: 
        public AddFavoriteResponse AddUserFavourite(AddFavoriteRequest addFavoriteRequest)
        {
            AddFavoriteResponse resp = new AddFavoriteResponse();            
            resp.Message = null;
            resp.Result = false;
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            //Check UserId, PageNumber and ChapterNumber valid
            if (addFavoriteRequest.UserId > 0 && addFavoriteRequest.PageNumber > 0 && addFavoriteRequest.ChapterNumber > 0)
            {
                //Check Favorite record exist in UserFavorite table or not                
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, addFavoriteRequest.UserId);
                DBUtility.AddSqlParam(sqlParam, "@PageNumber", SqlDbType.BigInt, addFavoriteRequest.PageNumber);
                DBUtility.AddSqlParam(sqlParam, "@ChapterNumber", SqlDbType.BigInt, addFavoriteRequest.ChapterNumber);
                sqlStr = @"select uf.Id
                                from UserFavorites uf
                                join PageInfo pi on pi.Id = uf.PageInfo                                     
                                join Chapter c on c.Id = pi.Chapter
                                where uf.UserId = @UserId and uf.PageNumber = @PageNumber and c.Number = @ChapterNumber";
                DataSet favoriteSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                if (DBUtility.hasResult(favoriteSet))
                {
                    resp.Message = "User Favorite exist. UserID " + addFavoriteRequest.UserId +
                                        " Chapter# " + addFavoriteRequest.ChapterNumber +
                                        " Page# " + addFavoriteRequest.PageNumber;
                    resp.Result = true;
                }
                else
                {//add Favorite into UserFavorites table                    
                    sqlStr = @"select pi.Id as PageInfo
                                from PageInfo pi
                                join Chapter c on c.Id = pi.Chapter
                                where c.Number = @ChapterNumber and pi.Number = @PageNumber";
                    int pageInfo = 0;
                    DataSet pageInfoSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                    if (DBUtility.hasResult(pageInfoSet))
                    {
                        pageInfo = Convert.ToInt32(pageInfoSet.Tables[0].Rows[0]["PageInfo"]);

                        sqlParam.Clear();
                        DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, addFavoriteRequest.UserId);
                        DBUtility.AddSqlParam(sqlParam, "@PageInfo", SqlDbType.BigInt, pageInfo);
                        DBUtility.AddSqlParam(sqlParam, "@PageNumber", SqlDbType.BigInt, addFavoriteRequest.PageNumber);
                        DBUtility.AddSqlParam(sqlParam, "@FavoriteName", SqlDbType.NVarChar, addFavoriteRequest.FavoriteName);
                        DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now);
                        DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
                        DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");
                        sqlStr = @"insert into UserFavorites(UserId, PageInfo, PageNumber, FavoriteName, CreatedOn, LastModifiedDate, LastModifiedBy)
                                        values (@UserId, @PageInfo, @PageNumber, @FavoriteName, @CreatedOn, @LastModifiedDate, @LastModifiedBy);";
                        if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                        {
                            throw new ServiceException("Insert UserFavorites failed. UserID: " + addFavoriteRequest.UserId);
                        }
                        resp.Message = "UserFavorite inserted. UserID: " + addFavoriteRequest.UserId +
                                                "; Chapter#: " + addFavoriteRequest.ChapterNumber +
                                                "; Page#: " + addFavoriteRequest.PageNumber; ;
                        resp.Result = true;
                    }
                    else
                    {
                        resp.Message = "No Page exist";
                        resp.Result = false;
                    }
                }
            }
            return resp;
        }

        public GetFavoriteResponse GetUserFavorite(GetFavoriteRequest getUserFavoriteRequest)
        {
            GetFavoriteResponse resp = new GetFavoriteResponse();
            List<FavoriteInfo> favoriteList = new List<FavoriteInfo>();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, getUserFavoriteRequest.UserId);            
            sqlStr = @"select 
                           uf.Id as Id,
                           uf.FavoriteName as FavoriteName,					 
                           pi.Number as PageNumber,                    
                           c.Number as ChapterNumber                           
					  from UserFavorites uf                                    				     					       
                           join PageInfo pi on pi.Id = uf.PageInfo                          
					       join Chapter c on c.Id = pi.Chapter					       
                           where uf.UserId = @UserId";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if (DBUtility.hasResult(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    FavoriteInfo info = new FavoriteInfo();
                    info.UserFavoriteId = Convert.ToInt32(dr["Id"]);
                    info.FavoriteName = dr["FavoriteName"].ToString();
                    info.PageNumber = Convert.ToInt32(dr["PageNumber"]);
                    info.ChapterNumber = Convert.ToInt32(dr["ChapterNumber"]);
                    favoriteList.Add(info);
                }
            }
            resp.FavoriteList = favoriteList;          
            return resp;
        }
        
        public DeleteFavoriteResponse DeleteUserFavorite(DeleteFavoriteRequest deleteUserFavoriteRequest)
        {
            DeleteFavoriteResponse resp = new DeleteFavoriteResponse();
            resp.Message = null;
            resp.Result = false;           
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            List<string> sqlSubStr = new List<string>();            
            int pageInfo = 0;
            if(deleteUserFavoriteRequest.UserId > 0)
            {
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, deleteUserFavoriteRequest.UserId);
                sqlSubStr.Add("UserId=@UserId");
            }
            else
            {
                throw new ServiceException("UserID is invalid. UserId:" + deleteUserFavoriteRequest.UserId);
            }
            if(deleteUserFavoriteRequest.PageNumber > 0)
            {//Page number is valid
                DBUtility.AddSqlParam(sqlParam, "@PageNumber", SqlDbType.BigInt, deleteUserFavoriteRequest.PageNumber);
                sqlSubStr.Add("PageNumber = @PageNumber");
                if(deleteUserFavoriteRequest.ChapterNumber > 0)
                {//Find pageInfo if ChapterNumber and PageNumber are avaliable
                    DBUtility.AddSqlParam(sqlParam, "@ChapterNumber", SqlDbType.BigInt, deleteUserFavoriteRequest.ChapterNumber);
                    sqlStr = @"select pi.Id as PageInfo
                                from PageInfo pi
                                join Chapter c on c.Id = pi.Chapter
                                where c.Number = @ChapterNumber and pi.Number = @PageNumber";
                    DataSet pageIdSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                    if(DBUtility.hasResult(pageIdSet))
                    {
                        pageInfo = Convert.ToInt32(pageIdSet.Tables[0].Rows[0]["PageInfo"]);

                        DBUtility.AddSqlParam(sqlParam, "@PageInfo", SqlDbType.BigInt, pageInfo);
                        sqlSubStr.Add("PageInfo = @PageInfo");
                    }
                }                
            }
            else
            {//No PageNumber input
                if(deleteUserFavoriteRequest.ChapterNumber > 0)
                {//Chapter number is valid and find PageInfo ID
                    DBUtility.AddSqlParam(sqlParam, "@ChapterNumber", SqlDbType.BigInt, deleteUserFavoriteRequest.ChapterNumber);
                    sqlStr = @"select pi.Id as PageInfo 
                                from PageInfo pi
                                join Chapter c on c.Id = pi.Chapter 
                                where c.Number = @ChapterNumber";
                    DataSet pageIdSet = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                    if(DBUtility.hasResult(pageIdSet))
                    {//Get the first one only
                        pageInfo = Convert.ToInt32(pageIdSet.Tables[0].Rows[0]["PageInfo"]);

                        DBUtility.AddSqlParam(sqlParam, "@PageInfo", SqlDbType.BigInt, pageInfo);
                        sqlSubStr.Add("PageInfo = @PageInfo");
                    }                    
                }
            }
            if(deleteUserFavoriteRequest.FavoriteName != null && deleteUserFavoriteRequest.FavoriteName.Length > 0)
            {
                DBUtility.AddSqlParam(sqlParam, "@FavoriteName", SqlDbType.NVarChar, deleteUserFavoriteRequest.FavoriteName);
                sqlSubStr.Add("FavoriteName = @FavoriteName");                
            }
            if (sqlSubStr.Count > 0)
            {
                sqlStr = @"delete from UserFavorites where" + " ";
                foreach (string str in sqlSubStr)
                {
                    sqlStr += str + " " + "and" + " ";
                }
                sqlStr = sqlStr.Remove(sqlStr.Length - 4);
                try
                {
                    log.Debug("delete user favorites. SQL: " + sqlStr);
                    if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                    {
                        throw new ServiceException("Delete user favorite failed. UserID: " + deleteUserFavoriteRequest.UserId);
                    }
                    resp.Message = "Delete user favorite succeed.";
                    resp.Result = true;
                    //return resp;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            return resp;
        }

        public PurchaseCoinsResponse PurchaseCoin(PurchaseCoinsRequest purchaseCoinRequest)
        {
            PurchaseCoinsResponse resp = new PurchaseCoinsResponse();            
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = null;
            int coin = 0;
            DBUtility.AddSqlParam(sqlParam, "@Id", SqlDbType.BigInt, purchaseCoinRequest.UserId);
            sqlStr = @"select Coin from UserInfo where Id = @Id";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                coin = Convert.ToInt32(ds.Tables[0].Rows[0]["Coin"]);                
            }
            coin += purchaseCoinRequest.PurchasedCoin;
            DBUtility.AddSqlParam(sqlParam, "@Coin", SqlDbType.BigInt, coin);
            DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString());
            sqlStr = @"Update UserInfo set Coin=@Coin, CreatedOn = @CreatedOn where Id = @Id";
            try
            {
                log.Debug("Update user Coins. SQL: " + sqlStr);
                if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                {
                    throw new ServiceException("Update User purchased coin failed. UserID: " + purchaseCoinRequest.UserId);
                }
                resp.Coins = coin;
                return resp;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Debugging method
        /// </summary>
        /// <param name="getUserActionRequest"></param>
        /// <returns></returns>
        public GetUserActionResponse GetUserActionById(GetUserActionRequest getUserActionRequest)
        {
            GetUserActionResponse resp = new GetUserActionResponse();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            List<UserActionInfo> userActionList = new List<UserActionInfo>();
            string sqlStr = null;
            if (getUserActionRequest.Action > 0)
            {
                sqlStr = @"select Id, UserId, Action, Parameter, FulfilledDateTime from UserAction where UserId = @UserId and Action = @Action";
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, getUserActionRequest.UserId);
                DBUtility.AddSqlParam(sqlParam, "@Action", SqlDbType.BigInt, getUserActionRequest.Action);
            }
            else
            {
                sqlStr = @"select Id, UserId, Action, Parameter, FulfilledDateTime from UserAction where UserId = @UserId";
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, getUserActionRequest.UserId);
            }
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    UserActionInfo userAction = new UserActionInfo();
                    userAction.Id = Convert.ToInt32(dr["Id"]);
                    userAction.UserId = Convert.ToInt32(dr["UserId"]);
                    userAction.Action = Convert.ToInt32(dr["Action"]);
                    userAction.Parameter = dr["Parameter"].ToString();
                    userAction.FulfilledDateTime = Convert.ToDateTime(dr["FulfilledDateTime"]);
                    userActionList.Add(userAction);
                }
            }
            resp.UserActionList = userActionList;
            return resp;
        }
    }
}